<?php

if (!class_exists('udba')):

    class statementHandle {

        var $dbms;
        var $con;
        var $sth;
        var $freed;

        function statementHandle ($dbms,$con,$sth) {
            $this->dbms  = $dbms;
            $this->con   = $con;
            $this->sth   = $sth;
            $this->freed = false;
        }

        function free () {
            if ($this->sth && !$this->freed) {
                switch ($this->dbms) {
                    case 'mysql':
                        mysql_free_result($this->sth);
                        $this->sth = 0;
                        break;
                }
                $this->freed = true;
            }
        }

        function setCursor ($index) {
            if ($this->sth) {
                switch ($this->dbms) {
                    case 'mysql':
                        return mysql_data_seek($this->sth,$index);
                        break;
                }
            }
        }

        function num_rows () {
            switch ($this->dbms) {
                case 'mysql':
                    if ($this->sth)
                        return mysql_num_rows($this->sth);
                    else
                        return 0;
                    break;
            }
        }

        function fetch_row () {
            switch ($this->dbms) {
                case 'mysql':
                    return mysql_fetch_row($this->sth);
                    break;
            }
        }

        function fetch_rows () {
            switch ($this->dbms) {
                case 'mysql':
                    $result = array();
                    for ($i=0;$i<$this->num_rows();$i++) {
                        $result[] = mysql_fetch_row($this->sth);
                    }
                    $this->free();
                    return $result;
                    break;
            }
        }

        function fetch_row_array () {
            switch ($this->dbms) {
                case 'mysql':
                    return @mysql_fetch_array($this->sth, MYSQL_ASSOC);
                    break;
            }
        }

        function fetch_rows_array () {
            $result = array();
            switch ($this->dbms) {
                case 'mysql':
                    for ($i=0;$i<$this->num_rows();$i++) {
                        $result[] = mysql_fetch_array($this->sth, MYSQL_ASSOC);
                    }
                    $this->free();
                    return $result;
                    break;
            }
        }

    }


    class udba extends statementHandle { // universal dbaccess

    // Stellen nach dem Komma bei float in dBase
        var $dBasePrecision = 2;
        private $errorstable;

        
        // encodings: http://dev.mysql.com/doc/refman/5.1/en/charset-charsets.html
        function udba ($dbase,$dbhost,$dbusr,$dbpass,$tableforwarder,$encoding='latin1',$dbms = 'mysql') {
            $this->tableforwarder = $tableforwarder;
            $this->tableprefix    = $tableforwarder;
            $this->dbms           = $dbms;
            $this->database       = $dbase;
            $this->host           = $dbhost;
            $this->user           = $dbusr;
            $this->pwd            = $dbpass;
            $this->encoding       = $encoding?$encoding:'latin1';
            $this->errorstable = array(
                'mysql' => array(
                    1022 => 'ER_DUP_KEY', // duplicate key
                    1040 => 'ER_CON_COUNT_ERROR', // Too many connections
                    1046 => 'ER_NO_DB_ERROR', // no db selected
                    1049 => 'ER_BAD_DB_ERROR', // unknown database
                    1050 => 'ER_TABLE_EXISTS_ERROR', // table already exists
                    1052 => 'ER_NON_UNIQ_ERROR', // Column is ambiguous
                    1054 => 'ER_BAD_FIELD_ERROR', // unknown column
                    1058 => 'ER_WRONG_VALUE_COUNT', // Column count doesn't match value count
                    1062 => 'ER_DUP_ENTRY', // Duplicate entry '%s' for key %d
                    1064 => 'ER_PARSE_ERROR', // SQL parse error
                    1071 => 'ER_TOO_LONG_KEY', // Specified key was too long
                    1074 => 'ER_TOO_BIG_FIELDLENGTH', // Column length too big for column.
                    1109 => 'ER_UNKNOWN_TABLE', // Unknown table
                    1169 => 'ER_DUP_UNIQUE', // Can't write, because of unique constraint
                    1215 => 'ER_CANNOT_ADD_FOREIGN', // Cannot add foreign key constraint
                    1216 => 'ER_NO_REFERENCED_ROW', // Cannot add or update a child row: a foreign key constraint fails
                    1217 => 'ER_ROW_IS_REFERENCED', // Cannot delete or update a parent row: a foreign key constraint fails
                )
            );
            $this->connect();
        }

        /**
         * @return string Hostname
         */
        function getHost () {
            return $this->host;
        }

        /**
         * @return string Table prefix
         */
        function getTableprefix () {
            return $this->tableprefix;
        }

        /**
         * @return string Hostname
         */
        function getUser () {
            return $this->user;
        }

        /**
         * @return string Hostname
         */
        function getPwd () {
            return $this->pwd;
        }

        function connect ($dbase = '',$dbhost = '',$dbusr = '',$dbpass = '',$encoding = '') {
            if (!$dbase) {
                $dbase  = $this->database;
            }
            if (!$dbhost) {
                $dbhost = $this->host;
            }
            if (!$dbusr) {
                $dbusr  = $this->user;
            }
            if (!$dbpass) {
                $dbpass = $this->pwd;
            }
            if (!$dbpass) {
                $dbpass = $this->pwd;
            }
            if (!$encoding) {
                $encoding = $this->encoding;
            }
            switch ($this->dbms) {
                case 'mysql':
                    $this->con = mysql_pconnect($dbhost,$dbusr,$dbpass);
                    mysql_set_charset($encoding, $this->con);
                    if ($this->con) {
                        mysql_select_db($dbase,$this->con);
                    }
                    break;
            }
        }

        function get_imgextension ($img_mime) {
            $extension = '';
            if (preg_match("/image\/jpeg/i",$img_mime) || preg_match("/image\/jpg/i",$img_mime) || preg_match("/image\/pjpeg/i",$img_mime)) {
                $extension = '.jpg';
            } elseif (preg_match("/image\/gif/i",$img_mime)) {
                $extension = '.gif';
            } elseif (preg_match("/application\/x-shockwave-flash/i",$img_mime)) {
                $extension = '.swf';
            } elseif (preg_match("/image\/x-png/",$img_mime)) {
                $extension = '.png';
            } elseif (preg_match("/image\/png/",$img_mime)) {
                $extension = '.png';
            } elseif (preg_match("/video\/mp4/",$img_mime)) {
                $extension = '.mp4';
            }
            return $extension;
        }

        function quote ($str, $quotes = true) {
            //if (!get_magic_quotes_gpc())
                $str = addslashes($str);
            if ($quotes)
                return "'".$str."'";
            else
                return $str;
        }

        function getError () {
            switch ($this->dbms) {
                case 'mysql':
                    return mysql_error($this->con);
                    break;
            }
        }

        function getErrorType ($errorno) {
            return $this->errorstable[$this->dbms][$errorno];
        }

        function getErrorNo () {
            switch ($this->dbms) {
                case 'mysql':
                    return mysql_errno($this->con);
                    break;
            }
        }

        function query ($query,$verbose=false) {
            switch ($this->dbms) {
                case 'mysql':
                    unset($this->sth);
                    if ($verbose) print $query."\n";
                    $this->sth = @mysql_query($query,$this->con);
                    if ($this->sth) return new statementHandle($this->dbms,$this->con,$this->sth);
                    if (isset($GLOBALS['cfgDebugMode']) && $GLOBALS['cfgDebugMode'])
                        echo '<p>'.$query.'<br>'.$this->getError().'</p>';
                    break;
            }
        }

        function beginTransaction () {
            $this->query("START TRANSACTION;");
            if ($this->getErrorNo())
                throw new dba_SQLException ("Could not start transaction!");
        }

        function commitTransaction () {
            $this->query("COMMIT;");
            if ($this->getErrorNo())
                throw new dba_SQLException ("Could not commit transaction!");
        }

        function rollbackTransaction () {
            $this->query("ROLLBACK;");
            if ($this->getErrorNo())
                throw new dba_SQLException ("Could not rollback transaction!");
        }

        function affected_rows () {
            switch ($this->dbms) {
                case 'mysql':
                    return mysql_affected_rows($this->con);
                    break;
            }
        }

        function insert_id () {
            switch ($this->dbms) {
                case 'mysql':
                    return mysql_insert_id($this->con);
                    break;
            }
        }

        // private: get metadata for mysql
        function _mysql_meta_data ($table) {
            if ($table) {
                $result = @mysql_list_fields($this->database,$table,$this->con);
            } else {
                $result = $this->sth; // Statement-Handle
            }
            if ($result) {
                $count    = @mysql_num_fields($result);
                $meta = array();
                for ($i=0;$i<$count;$i++) {
                    $meta[$i]['table']  = @mysql_field_table($result,$i);
                    $meta[$i]['name']   = @mysql_field_name($result,$i);
                    $meta[$i]['type']   = @mysql_field_type($result,$i);
                    $meta[$i]['len']    = @mysql_field_len($result,$i);
                    $meta[$i]['flags']  = @mysql_field_flags($result,$i);
                }
                return $meta;
            }
        }

        function getMetadata ($table = '') {
            switch ($this->dbms) {
                case 'mysql':
                    return $this->_mysql_meta_data($table);
                    break;
            }
        }


        // aufruf : getFieldnameArray(getMetadata('table'));
        function getFieldnameArray ($metadata) {
            $names = array();
            for ($i=0;$i<count($metadata);$i++) {
                $names[] = $metadata[$i]['name'];
            }
            return $names;
        }

        //  export/import funktionen
        //  CSV/dBase/bald BMEcat


        // fieldstring for insert-query
        function genFieldStr ($fields) {
            return $this->genSeparatedStr($fields);
        }

        function genUpdateFieldStr ($fieldnames,$fieldvalues = '') {
            if (!is_array($fieldvalues)) return $this->genUpdateFieldStrFromAssozArray($fieldnames);
            if (count($fieldnames) == count($fieldvalues)) {
                for ($i=0;$i<count($fieldnames);$i++) {
                    $pairs[] = $fieldnames[$i].'='.$this->quote($fieldvalues[$i]);
                }
                return $this->genSeparatedStr($pairs);
            } else {
                return false;
            }
        }

        function genUpdateFieldStrFromAssozArray ($fields) {
            $pairs = array();
            foreach ($fields as $n => $v) {
                $pairs[] = $n.'='.$this->quote($v);
            }
            return join(",", $pairs);
        }



        function genCSVStr ($fields,$separator = ',') {
            $fieldstr = '';
            $count = count($fields);
            for ($i = 0; $i < $count; $i++) {
                $fieldStr .= addslashes(preg_replace("/[\n\r;]/","",$fields[$i]));
                if ($i != $count-1) {
                    $fieldStr .= $separator;
                }
            }
            return $fieldStr;
        }

        function genSeparatedStr ($fields,$separator = ',') {
            $fieldStr = '';
            $count = count($fields);
            for ($i = 0; $i < $count; $i++) {
                if ($i == $count-1) {
                    $fieldStr .= $fields[$i];
                } else {
                    $fieldStr .= $fields[$i].$separator;
                }
            }
            return $fieldStr;
        }

        function genQuotedFieldStr ($fields) {
            $fieldStr = '';
            $count = count($fields);
            for ($i = 0; $i < $count; $i++) {
                if ($i == $count-1) {
                    $fieldStr .= $this->quote($fields[$i]);
                } else {
                    $fieldStr .= $this->quote($fields[$i]).",";
                }
            }
            return $fieldStr;
        }

        function importCSV ($filepath,$plattform,$table,$separator = ';') {
            $this->convertCSV($filepath,'tmp.csv',$plattform);
            $fp    = fopen('tmp.csv',"r");
            $i     = 0;
            $query = "insert into $table values(";
            while($dset = fgetcsv($fp,1200000,$separator)) {
                $i++;
                $this->query($query.$this->genQuotedFieldStr($dset).");");
                $dberror = $this->getError();
                if ($dberror) {
                    $this->importErrors[] = 'DB-Error: '.$dberror;
                    $dberror  = '';
                }
            }
            syn_unlink('tmp.csv');
        }

        function printImportErrors () {
            if(is_array($this->importErrors)) {
                while (list( ,$value) = each($this->importErrors)) {
                    echo $value.'<br>';
                }
            }
        }

        function importCSVFields ($filepath,$plattform,$table,$fields,$separator = ';') {
            $this->convertCSV($filepath,'tmp.csv',$plattform);
            $fp    = fopen('tmp.csv',"r");
            $i     = 0;
            $query = "insert into $table (".$this->genFieldStr($fields).") values(";
            while($dset = fgetcsv($fp,1200000,$separator)) {
                $i++;
                if (count($dset) != count($fields)) {
                    $this->importErrors[] = "Error: Feldanzahlen stimmen nicht �berein";
                }
                $this->query($query.$this->genQuotedFieldStr($dset).");");
                $dberror = $this->getError();
                if ($dberror) {
                    $this->importErrors[] = 'DB-Error: '.$dberror;
                    $dberror  = '';
                }
            }
            syn_unlink('tmp.csv');
        }
        // fields erstes feld immer die id
        function updateCSVFields ($filepath,$plattform,$table,$fields,$separator = ';') {
            $this->convertCSV($filepath,'tmp.csv',$plattform);
            $fp    = fopen('tmp.csv',"r");
            $i     = 0;
            $query = "Update $table set ";
            $f_id  = array_shift($fields); // Feldname der id
            while($dset = fgetcsv($fp,1200000,$separator)) {
                $i++;
                $id  = array_shift($dset);
                $pairs = $this->genUpdateFieldStr($fields,$dset);
                if ($pairs) {
                    $this->query($query.$this->genFieldStr($pairs)." where $f_id=$id;");
                    $dberror = $this->getError();
                    if ($dberror) {
                        $this->importErrors[] = 'DB-Error: '.$dberror;
                        $dberror  = '';
                    }
                }
            }
            syn_unlink('tmp.csv');
        }

        // orginalfile ; neuer File ; plattform des erzeugenden Rechners
        function convertCSV ($original,$new,$plattform) {
            $fp  = fopen($original,'r');
            if (file_exists($new)) {
                syn_unlink($new);
            }
            $nfp = fopen($new,'a');
            if ($plattform == 'pc') {
                $newline = "\r\n";
            } elseif ($plattform == 'mac') {
                $newline = "\r";
            }
            while ($line = fgets($fp,4096)) {
                fputs($nfp,preg_replace("/\r\n/","\n",$line));
            }
            fclose($fp);
            fclose($nfp);
        }

        function exportCSV ($filepath,$table,$fields,$targetplatform,$separator = ';') {
            $fieldStr = '';
            if ($fields) {
                $fieldStr = $this->genFieldStr($fields);
            } else {
                $fieldStr = '*';
            }
            $check    = $this->query("select $fieldStr from $table;");
            $num_rows = $this->num_rows();
            if (file_exists($filepath)) {
                syn_unlink($filepath);
            }
            if ($targetplatform == 'pc') {
                $newline = "\r\n";
            } elseif ($targetplatform == 'mac') {
                $newline = "\r";
            } elseif ($targetplatform == 'unix') {
                $newline = "\n";
            }
            $fp       = fopen($filepath,"a");
            rewind($fp);
            for ($i=0;$i<$num_rows;$i++) {
                $dset = $this->fetch_row();
                fputs($fp,$this->genCSVStr($dset,';').$newline);
            }
            $this->free();
        }

        // dbase functionen

        // generiert dBase Table-Def aus $metadata: direkt aus getmetadata()
        function gendBaseDef ($metadata,$fieldmap) {
            $def = array();
            for ($i=0;$i<count($metadata);$i++) {
                $field[0] = $fieldmap[$metadata[$i]['name']];
                switch ($metadata[$i]['type']) {
                    case 'string':
                        $field[1] = "C";
                        $field[2] = $metadata[$i]['len'];
                        break;
                    case 'enum':
                        $field[1] = "C";
                        $field[2] = 1;
                        break;
                    case 'int':
                        $field[1] = "N";
                        $field[2] = $metadata[$i]['len'];
                        $field[3] = 0;
                        break;
                    case 'real':
                        $field[1] = "N";
                        $field[2] = $metadata[$i]['len'];
                        $field[3] = $this->dBasePrecision;
                        break;
                    case 'blob':
                        $field[1] = "C";
                        $field[2] = $metadata[$i]['len'];
                        break;
                }
                $def[] = $field;
            }
            return $def;
        }


        function exportArticledBase ($filepath,$table,$fields) {
            $mappingdata = array('realnames' => array('id','orderno','name','lang','short_descr','listimg_url','listimg_height','listimg_width','listimg_type','amount','infoimg_url','infoimg_height','infoimg_width','infoimg_type','long_descr','visible','display_url','kg','packet','mwst_id'),
                'map' => array('id','orderno','name','lang','shortdescr','listb_url','listb_h','listb_w','listb_t','price','infob_url','infob_h','infob_w','infob_t','longdescr','visible','template','weight','packet','tax_id'));
            for ($i=0;$i<count($mappingdata['realnames']);$i++) {
                for ($j=0;$j<count($fields);$j++) {
                    if ($fields[$j] == $mappingdata['realnames'][$i]) {
                        $fieldsarr['realnames'][] = $mappingdata['realnames'][$i];
                        $fieldsarr['map'][]       = $mappingdata['map'][$i];
                    }
                }
            }
            $this->exportdBase($filepath,$table,$fieldsarr);
        }


        // exportiert DB als dBase                          !Vorsicht! entsprechender Mapname muss gleichen Arrayindex haben wie Realname
        // Dateiname, Tabellenname, Felder array of array : ['realnames'] = felder namen, ['map'] = neue feldernamen : wg. dBase h�chstens 10 Zeichen
        function exportdBase ($filepath,$table,$fields) {
            if(count($fields['realnames']) != count($fields['map'])) {print "<strong>\"realnames\" und \"map\" sind unterschiedlich lang!</strong><br>"; return;}
            if ($fields) {
                $fieldStr = $this->genFieldStr($fields['realnames']);
            } else {
                $fieldStr = '*';
            }
            for ($i=0;$i<count($fields['realnames']);$i++) {
                $fieldmap[$fields['realnames'][$i]] = $fields['map'][$i];
            }
            $check    = $this->query("select $fieldStr from $table;");
            $num_rows = $this->num_rows();
            if (file_exists($filepath)) {
                syn_unlink($filepath);
            }
            $dBaseDef = $this->gendBaseDef($this->getMetadata(),$fieldmap);
/*   for ($i=0;$i<count($dBaseDef);$i++){
      for ($j=0;$j<count($dBaseDef[$i]);$j++){
         echo $dBaseDef[$i][$j];
      }
      echo "<br>";
   }*/
            if (!dbase_create($filepath, $dBaseDef))
                print "<strong>Error creating dBase-File!</strong>";

            $dbh = dbase_open($filepath,2);
            if ($dbh) {
                for ($i=0;$i<$num_rows;$i++) {
                    $dset = $this->fetch_row();
                    dbase_add_record($dbh,$dset);
                }
            }
            dbase_close($dbh);
        }

        function fetchdBase ($original,$new) {
            if (file_exists($new)) {
                syn_unlink($new);
            }
            if (!copy($original, $new)) {
                print ("konnte $original nicht kopieren...<br>\n");
            }
        }

        function importdBase ($filepath,$table) {
            $this->fetchdBase($filepath,'tmp.dbf');
            $dbh = dbase_open ('tmp.dbf', 0);
            $num_rec = dbase_numrecords ($dbh);
            for ($i=1;$i<=$num_rec;$i++) {
                $dset = dbase_get_record_with_names   ($dbh, $i);
                while (list (,$value) = each($dset)) {
                    $fp = fopen("test.html","a");
                    fputs ($fp,$value."<br>\n",500);
                    fclose($fp);
                }
            }
        }

        function close () {
            switch ($this->dbms) {
                case 'mysql':
                    return mysql_close($this->con);
                    break;
            }
            $this->con = 0;
            $this->sth = 0;
        }

    }

endif;
?>
